let { Service } = require('egg')
const { mysql } = require('../../config/plugin')

class LabService extends Service {
  // 分页获取实验室(弃用)
  async getLabPage(params) {
    let { mysql } = this.app
    let { pageNo, pageSize, labName } = params
    pageNo = parseInt(pageNo)
    pageSize = parseInt(pageSize)
    let res;
    let totalRes;
    // console.log(pageNo,pageSize,labName);
    // 没有名字
    if (!labName) {
      let sql1 = `select * from  laboratory  limit ? offset ?`
      let sql2 = `select count(*) total from laboratory `
      //  获取查询到的数据
      res = await mysql.query(sql1, [pageSize, (pageNo - 1) * pageSize])
      // 获取数据总量
      totalRes = await mysql.query(sql2)



    } else {
      // 有名字
      let sql1 = `select * from  laboratory where labName like ? limit ? offset ?`
      let sql2 = `select count(*) total from laboratory where  labName like ?`
      //  获取查询到的数据
      res = await mysql.query(sql1, ['%' + labName + '%', pageSize, (pageNo - 1) * pageSize])
      // 获取数据总量
      totalRes = await mysql.query(sql2, ['%' + labName + '%'])
    }
    return {
      list: res,
      total: totalRes[0].total
    }
  }

  //收藏实验室
  async collectLab(userId, laboratoryId) {
    let { mysql } = this.app
    // 先检查实验室是否存在
    let labInfo = await mysql.get('laboratory', { laboratoryId })
    if (!labInfo) {
      return "该实验室不存在"
    } else {
      let collectInfo = await mysql.get('collect', { userId, laboratoryId })
      if (collectInfo) {
        return "实验室已收藏"
      } else {
        let res = await mysql.insert('collect', { userId, laboratoryId })
        // console.log(res);
        if (res.affectedRows === 1) {
          return "收藏成功"
        } else {
          return "收藏失败"
        }
      }
    }


  }
  // 取消收藏实验室
  async collectLabDel(userId, laboratoryId) {
    let { mysql } = this.app
    laboratoryId = parseInt(laboratoryId)
    let sql = `delete from collect where userId = ? and laboratoryId = ? `
    let res = await mysql.query(sql, [userId, laboratoryId])
    // console.log(res);
    if (res.affectedRows === 1) {
      return "取消成功"
    } else {
      return "取消失败"
    }
  }

  // 新建实验室
  async createLab(labName, volume, address, typeId,intro, photo, isExamine, selectTimes,eqList) {
    let { mysql } = this.app
    let typeInfo = await mysql.get('labType', { typeId })
    if (!typeInfo) {
      return "该实验室类型不存在"
    }
    let res = await mysql.insert('laboratory', { labName, volume, address, typeId, intro, photo, isExamine })
    if (res.affectedRows === 1) {
      // 将该实验室的可预约时间段存入到对应的表中
      let laboratoryId = res.insertId
      let arr = [...selectTimes]
      arr.forEach(async (item, index) => {
        // console.log(item);
        let date = new Date().getTime()
        item.laboratoryId = laboratoryId
        // 就是做个标记而已
        item.createTime = date + index
        item.tStatus = 0
      })
      let arr2 = [...arr]
      for (let i = 0; i < arr2.length; i++) {
        // 插入可选预约时间
        let res1 = await mysql.insert('timeoption', { laboratoryId: arr2[i].laboratoryId, start: arr2[i].start, end: arr2[i].end, createTime: arr2[i].createTime, })
      }

      // 该到实验室设备了
      let arr4 = [...eqList]
      arr4.forEach((item,index)=>{
        let date = new Date().getTime()
        item.laboratoryId = laboratoryId
        // 就是做个标记而已
        item.createTime = date + index
      })

      let arr5 = [...arr4]
      for(let i=0;i<arr5.length;i++){
        let res2 = await mysql.insert('equipment',{equName:arr5[i].equName,number:arr5[i].number,laboratoryId,createTime:arr5[i].createTime})
      }

      return "创建成功"
    } else {
      return "创建失败"
    }

  }

  // 更新实验室
  async updateLab(laboratoryId, labName, volume, address, typeId, intro, photo, isExamine, selectTimes,eqList) {
    let creatRes
    let delRes
    let changeRes
    let { mysql } = this.app
  
    // 核心：时间段被删除或修改了，相关联的所有预约信息包括：预约记录、当日可预约时间全部作废
    // 查询出实验室所有相关的可预约时间段
    let sql = `select * from timeoption where laboratoryId = ?`
    let resops = await mysql.query(sql, [laboratoryId])

    // 先做删除操作，只要遍历没有相关createTime的就给删除掉
    let arr = [...resops]
    let arr1 = [...selectTimes]
 
    // 标记三个基础状态 这是从数据库查出来的数据
    // 给前端传递过来的数据标记上新旧
    arr1.forEach((item, index) => {
      if (!item.createTime) {
        let date = new Date().getTime()
        item.createTime = date + index
        item.isNew = true
      } else {
        item.isNew = false
      }
    })
    // 遍历数据库数据 添加基本标签 删除、更新
    arr.forEach((item, index) => {
      item.isDel = true
      item.isChange = false
    })
    // 更新
    // 判断是被修改了还是被删除了
    arr.forEach((item, index) => {
      arr1.forEach(async (item1, index1) => {
        // 如果不是新增的
        if (item.createTime == item1.createTime) {
          // 标记为未被删除
          item.isDel = false
          if (item.start != item1.start || item.end != item1.end) {
            // 标记为已经被修改
            item.isChange = true
            // 直接在这里更新
            let options = {
              where: {
                timeId: item.timeId
              }
            }
            changeRes = await mysql.update('timeoption', { start: item1.start, end: item1.end }, options)
            // 同时将所有dayTime表中关于该时间段的记录，tStatus全部作废
            // 如果我更新了可选时间段，那我的和这个option相关的dayTime该怎么办？不用作废我觉得，直接连同预约也给更新了不就得了
            // await mysql.update('dayTime', { tStatus: 3 }, options)
          }
        }

      })
    })

    // 接下来将新增的插入到数据库中
    arr1.forEach(async (item, index) => {
      if (item.isNew) {
        creatRes = await mysql.insert('timeoption', { laboratoryId: laboratoryId, start: item.start, end: item.end, createTime: item.createTime })
      }
    })
    // 删除
    // 遍历数据库查到的可预约时间段，有删除的就删除，没动的就不用动
    arr.forEach(async (item, index) => {
      if (item.isDel) {
        let options = {
          where: {
            timeId: item.timeId
          }
        }

        // 同时将所有dayTime表中关于该时间段的记录，tStatus全部作废
        // await mysql.update('dayTime', { tStatus: 3 }, options)
        // 先做一版本删除掉所有相关的daytime 和预约记录的
        // 先删除掉所有的预约记录
        sql = `delete from reservation where dayTimeId in (select timeId from daytime where timeOpId = ?)`
        await mysql.query(sql,[item.timeId])
        // 删除相关daytime
        await mysql.delete('daytime',{timeOpId:item.timeId})
         //最后 删除自己
        delRes = await mysql.delete('timeoption', { timeId: item.timeId })
        
      }

    })


    // 下边更新实验室设备
    let sarr = [...eqList]
    //  查询出实验室的所有相关设备
    let sqls1 = `select * from equipment where laboratoryId = ?`
    let arrEq = await mysql.query(sqls1,[laboratoryId])
        // 先用一个列表获取到所有的前端传来的设备的id
        let idList = []
        for(let i=0; i<sarr.length; i++){
            if(sarr[i].equipmentId){
              idList.push(sarr[i].equipmentId)
              // 更新
              let options = {
                where :{
                  equipmentId:sarr[i].equipmentId
                }
              }
              await mysql.update('equipment',{equName:sarr[i].equName,number:sarr[i].number},options)
            }
        }
   
    


    //  新增操作，谁没有equipmentId，就是新增的
    for(let i=0;i<sarr.length;i++){
      if(!sarr[i].equipmentId){
        let date = new Date().getTime()
        date = date+i
        await mysql.insert('equipment',{laboratoryId,equName:sarr[i].equName,number:sarr[i].number,createTime:date})
      }
    }



    //  再看查出来的所有设备中，谁的id不在里边的，就删除掉那个设备
    for(let i=0; i<arrEq.length; i++){
      if(idList.indexOf(arrEq[i].equipmentId)==-1){
        // arrEq[i].isDel = true
        // 表示被删除了，那同时就要删除掉相关的实验室设备
        await mysql.delete('equipment',{equipmentId:arrEq[i].equipmentId})
      }
    }



    let options = {
      where: {
        laboratoryId: laboratoryId
      }
    }
    let res = await mysql.update('laboratory', { labName, volume, address, typeId, intro, photo, isExamine }, options)
    if (res.affectedRows === 1) {
      return "更新成功"
    } else {
      return "更新失败"
    }

  }
  // 删除实验室
  async delLab(laboratoryId) {
    let { mysql } = this.app
    // 得先作废掉实验室当日可预约时间段,然后再删除实验室
    // let sql = `update dayTime set tStatus=? where timeOpId in (select timeId from timeoption where laboratoryId = ?)`
    // let resChange = await mysql.query(sql, [3, laboratoryId])

    // 删除掉相关实验室收藏记录

    let sql5 = `delete from collect where laboratoryId = ?`
    let delC = await mysql.query(sql5,[laboratoryId])
    
    // 先删掉预约
    let sql4 = `delete from reservation where  dayTimeId in(select timeId from daytime where timeOpId in (select timeId from timeoption where laboratoryId = ?))`
    let delRes = await mysql.query(sql4,[laboratoryId])

    // let res = await mysql.delete('laboratory', { laboratoryId })
    // if (res.affectedRows === 1) {
    //   return "删除成功"

    // } else {
    //   return "删除失败"
    // }

        //  同时删除掉相关的实验室设备记录
        let sql2 = `delete from equipment where laboratoryId = ?`
        let resDel1 = await mysql.query(sql2,[laboratoryId])
    
    // 删除实验室同时删掉相关的可预约时间段
    let sql1 = `delete l,t from laboratory l,timeoption t where t.laboratoryId=l.laboratoryId and l.laboratoryId=?`
    let resDel = await mysql.query(sql1, [laboratoryId])
    // if (resDel.affectedRows > 0) {
    //   return "删除成功"
    // } else {
    //   return "该实验室不存在或实验室id错误"
    // }

    //  同时作废掉已预约，但是没有过期的预约申请或成功的预约
    // let sql3 = `update  reservation set  status = ? where dayTimeId in(select timeId from daytime where timeOpId in (select timeId from timeoption where laboratoryId = ?))`
    // let updateRes = await mysql.query(sql3,[3,laboratoryId])
    // 删除掉相关的预约记录
    return "删除成功"
  }
  // 新增或修改实验室类别 属于类别管理
  async saveOrUpdateType(typeName, typeId) {
    let { mysql } = this.app
    if (!typeId) {
      // 先判断这个实验室类别是否已经存在
      let typeInfo = await mysql.get('labType', { typeName })
      if (typeInfo) {
        return "该实验室类别已存在"
      } else {
        // 新增
        let res = await mysql.insert('labType', { typeName })
        if (res.affectedRows === 1) {
          return "添加成功"
        } else {
          return "添加失败"
        }
      }

    } else if (typeName && typeId) {
      let typeInfo = await mysql.get('labType', { typeId })
      if (typeInfo) {
        // 修改
        let options = {
          where: {
            typeId
          }

        }
        let res = await mysql.update('labType', { typeName }, options)
        if (res.affectedRows === 1) {
          return "修改成功"
        } else {
          return "修改失败"
        }
      } else {
        return "不存在该实验室类别"
      }

    } else {
      return "缺少必填字段typeName"
    }

  }



  // 删除实验室类别
  async deleteType(typeId) {
    typeId = parseInt(typeId)
    let { mysql } = this.app
    // 先将相关类别的实验室的当日可预约记录作废
    // 作废
    // let sql = 'update dayTime d set tStatus = ? where d.timeOpId in (select timeId from timeoption t where t.laboratoryId in (select laboratoryId from laboratory l where typeId = ?) )'
    // let res1 = await mysql.query(sql, [3, typeId])
    //  删除所有相关的预约
    let  sql = 'delete from reservation where dayTimeId in (select timeId from daytime where timeId in (select timeId from timeoption where laboratoryId in (select laboratoryId from laboratory where typeId = ?)) )'
    let res1 = await mysql.query(sql,[typeId])

    // 删除类型相关的实验室和可预约时间段
    let sql1 = `delete a,b from laboratory a,timeoption b where a.typeId = ? and a.laboratoryId= b.laboratoryId `
    let res2 = await mysql.query(sql1, [typeId])
    // 删除类别
    let res3 = await mysql.delete('labType', { typeId })
    if (res3.affectedRows > 0) {
      return "删除成功"
    } else {
      return "该类别已删除或不存在"
    }


  }
  // 获取实验室详情
  async getLabDetail(laboratoryId) {
    let { mysql } = this.app
    // 先获取实验室基本信息
    let baseInfo = await mysql.get('laboratory', { laboratoryId })
    // let selectTimes = await mysql.get('timeoption', { laboratoryId })
    //  获取可预约时间段
    let sql = `select * from timeoption where laboratoryId = ?`
    let selectTimes = await mysql.query(sql, [laboratoryId])
    let obj = { ...baseInfo }
    obj.selectTimes = selectTimes
    //  获取实验室设备
    let sql1 = `select * from equipment where laboratoryId = ?`
    let eqList = await mysql.query(sql1,[laboratoryId])
    obj.eqList = eqList
    if (!baseInfo) {
      obj.message = "查询失败"
      return obj
    } else {
      obj.message = "查询成功"
      return obj
    }
  }
  // 创建或查询当日可预约时间段信息
  async createDayTime(dateTime, laboratoryId) {
    let { mysql } = this.app
    let labData = await mysql.get('laboratory', { laboratoryId })
    laboratoryId = parseInt(laboratoryId)

    // 先查是不是已经有列表了
    let sql1 = `select * from dayTime where dateTime = ? and timeOpId in (select timeId from timeoption where laboratoryId = ?)`
    let list = await mysql.query(sql1, [dateTime, laboratoryId])
    // 查询出所有可预约时间段
    let sql = `select * from timeoption where laboratoryId = ?`
    let selectTimes = await mysql.query(sql, [laboratoryId])
    let arr = [...selectTimes]
    let obj = {}
    if (list.length == 0) {
      let list2

      for (let i = 0; i < arr.length; i++) {
        await mysql.insert('dayTime', { timeOpId: arr[i].timeId, tVolume: labData.volume, baseVolume: labData.volume, nowCount: 0, tStatus: 0, dateTime })
      }
      let sql2 = `select * from dayTime where dateTime = ? and timeOpId in (select timeId from timeoption where laboratoryId = ?)`
      list2 = await mysql.query(sql2, [dateTime, laboratoryId])

      list2.forEach((item, index) => {
        item.start = arr[index].start
        item.end = arr[index].end
      })

      obj.message = "创建成功"
      obj.list = list2
      return obj

    } else {
      // 先看看有没有新增的时间段，有的话就将daytime创建上
      // 获取实验室全部的时间段选项
    let sqls = `select * from timeoption where laboratoryId = ?`
    let options = await mysql.query(sqls,[laboratoryId])
    // 遍历这些options,如果有哪个时间段没有创建当日可预约时间段的，就创建
    let arrs = [...options]
    for(let i=0;i<arrs.length;i++){
      let timeId = arrs[i].timeId
      let daytime = await mysql.get('daytime',{timeOpId:timeId})
      if(!daytime){
        // 如果获取不到的话，那就说明这个是新的option，现在要创建它
        await mysql.insert('dayTime', { timeOpId: timeId, tVolume: labData.volume, baseVolume: labData.volume, nowCount: 0, tStatus: 0, dateTime })
      }
    }
    // 再次查询出所有的daytime
    let sqls1= `select * from dayTime where dateTime = ? and timeOpId in (select timeId from timeoption where laboratoryId = ?)`
    let lists = await mysql.query(sqls1, [dateTime, laboratoryId])
      // console.log(list, "你好");
      let arr3 = []
     
      for (let i = 0; i < lists.length; i++) {
        let obj = { ...lists[i] }
        obj.start = arrs[i].start
        obj.end = arrs[i].end
        arr3.push(obj)
      }

      obj.message = "查询成功"
      obj.list = arr3
      return obj

    }

  }
  // 条件查询实验室
  async getLabList(labName, address, typeId, isExamine,laboratoryId) {
    let { mysql } = this.app
    let obj = {}
    if(laboratoryId){
      laboratoryId = parseInt(laboratoryId)
    }
    if(!laboratoryId){
          // 现在就只做名称查询和类别查询吧其他条件不太好做
    if (labName && typeId) {
      typeId = parseInt(typeId)
      let sql = 'select * from laboratory where labName like ? and typeId = ?'
      let sql1 = 'select count(*) total from laboratory where labName like ? and typeId = ?'
      let list = await mysql.query(sql, ['%' + labName + '%', typeId])
      let total = await mysql.query(sql1, ['%' + labName + '%', typeId])


      obj.list = list
      obj.total = total[0].total

    } else if (!labName && typeId) {
      typeId = parseInt(typeId)
      let sql = 'select * from laboratory where  typeId = ?'
      let sql1 = 'select count(*) total from laboratory where typeId = ?'
      let list = await mysql.query(sql, [typeId])
      let total = await mysql.query(sql1, [typeId])

      obj.list = list
      obj.total = total[0].total


    } else if (labName && !typeId) {
      let sql = 'select * from laboratory where labName like ?'
      let sql1 = 'select count(*) total from laboratory where labName like ?'
      let list = await mysql.query(sql, ['%' + labName + '%'])
      let total = await mysql.query(sql1, ['%' + labName + '%'])

      obj.list = list
      obj.total = total[0].total

    }
    else if (!labName && !typeId) {
      let sql = 'select * from laboratory'
      let sql1 = 'select count(*) total from laboratory'
      let list = await mysql.query(sql)
      let total = await mysql.query(sql1)

      obj.list = list
      obj.total = total[0].total


    }
    }else{
      let sql = 'select * from laboratory where laboratoryId = ?'
      let res = await mysql.query(sql,[laboratoryId])
      obj.list = res
      obj.total = 1
    }

    let arr = [...obj.list]
    for (let i = 0; i < arr.length; i++) {
      let result = await mysql.get('labtype', { typeId: arr[i].typeId })
      arr[i].typeName = result.typeName

    }

    return obj

  }

  // 获取所有实验室类别
  async getAllType(typeName,typeId) {
    let { mysql } = this.app
    let sql = 'select * from labtype'
    let list = await mysql.query(sql)
    //  id和typeName是互斥传入的
    if(typeName){
      let arr = [...list]
      let arr1=[]
      arr.forEach((item,index)=>{
        let str = item.typeName
        if(str.includes(typeName)){
          arr1.push(item)
        }
      })
      list = [...arr1]
    }
    if(typeId){
      let arr = [...list]
      let arr1=[]
      arr.forEach((item,index)=>{
        let id = item.typeId
        if(typeId==id){
          arr1.push(item)
        }
      })
      list = [...arr1]
    }

    return list
  }

  // 检查实验室类别的唯一性
  async checkTypeOnly(typeName){
    let  {mysql} = this.app
    let typeInfo = await mysql.get('labtype',{typeName})
    let obj = {}
    if(!typeInfo){
      obj.isOnly = true
    }else{
      obj.isOnly = false
    }
    return obj
  }

}


module.exports = LabService